package com.example.blogapi.dao;

import com.example.blogapi.model.Blog;
import com.example.blogapi.model.Tag;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class BlogDao {
    private final TagDao tagDao;

    public BlogDao() {
        this.tagDao = new TagDao();
    }

    public List<Blog> findAll() throws SQLException {
        String sql = "SELECT * FROM blogs ORDER BY created_at DESC";
        List<Blog> blogs = new ArrayList<>();

        try (Connection conn = DatabaseManager.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Blog blog = mapResultSetToBlog(rs);
                // 使用新的连接获取标签
                List<Tag> tags = new ArrayList<>();
                try {
                    tags = tagDao.findByBlogId(blog.getId());
                } catch (SQLException e) {
                    System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
                }
                blog.setTags(tags);
                blogs.add(blog);
            }
        }

        return blogs;
    }

    public Blog findById(Long id) throws SQLException {
        String sql = "SELECT * FROM blogs WHERE id = ?";

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Blog blog = mapResultSetToBlog(rs);
                    // 使用新的连接获取标签
                    List<Tag> tags = new ArrayList<>();
                    try {
                        tags = tagDao.findByBlogId(blog.getId());
                    } catch (SQLException e) {
                        System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
                    }
                    blog.setTags(tags);
                    return blog;
                }
            }
        }

        return null;
    }

    public List<Blog> findByUserId(Long userId) throws SQLException {
        String sql = "SELECT * FROM blogs WHERE user_id = ? ORDER BY created_at DESC";
        List<Blog> blogs = new ArrayList<>();

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, userId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Blog blog = mapResultSetToBlog(rs);
                    // 使用新的连接获取标签
                    List<Tag> tags = new ArrayList<>();
                    try {
                        tags = tagDao.findByBlogId(blog.getId());
                    } catch (SQLException e) {
                        System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
                    }
                    blog.setTags(tags);
                    blogs.add(blog);
                }
            }
        }

        return blogs;
    }

    public List<Blog> findByCategoryId(Long categoryId) throws SQLException {
        String sql = "SELECT * FROM blogs WHERE category_id = ? ORDER BY created_at DESC";
        List<Blog> blogs = new ArrayList<>();

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, categoryId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Blog blog = mapResultSetToBlog(rs);
                    // 使用新的连接获取标签
                    List<Tag> tags = new ArrayList<>();
                    try {
                        tags = tagDao.findByBlogId(blog.getId());
                    } catch (SQLException e) {
                        System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
                    }
                    blog.setTags(tags);
                    blogs.add(blog);
                }
            }
        }

        return blogs;
    }

    public List<Blog> findByTagId(Long tagId) throws SQLException {
        String sql = "SELECT b.* FROM blogs b " +
                "JOIN blog_tags bt ON b.id = bt.blog_id " +
                "WHERE bt.tag_id = ? ORDER BY b.created_at DESC";
        List<Blog> blogs = new ArrayList<>();

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, tagId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Blog blog = mapResultSetToBlog(rs);
                    // 使用新的连接获取标签
                    List<Tag> tags = new ArrayList<>();
                    try {
                        tags = tagDao.findByBlogId(blog.getId());
                    } catch (SQLException e) {
                        System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
                    }
                    blog.setTags(tags);
                    blogs.add(blog);
                }
            }
        }

        return blogs;
    }

    public Blog create(Blog blog, List<Long> tagIds) throws SQLException {
        String sql = "INSERT INTO blogs (title, content, user_id, category_id, created_at) VALUES (?, ?, ?, ?, ?)";

        Connection conn = null;
        try {
            conn = DatabaseManager.getConnection();
            conn.setAutoCommit(false);

            LocalDateTime now = LocalDateTime.now();
            blog.setCreatedAt(now);

            try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                pstmt.setString(1, blog.getTitle());
                pstmt.setString(2, blog.getContent());
                pstmt.setLong(3, blog.getUserId());
                pstmt.setLong(4, blog.getCategoryId());
                pstmt.setString(5, now.toString());

                int affectedRows = pstmt.executeUpdate();

                if (affectedRows == 0) {
                    throw new SQLException("Creating blog failed, no rows affected.");
                }

                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        blog.setId(generatedKeys.getLong(1));
                    } else {
                        throw new SQLException("Creating blog failed, no ID obtained.");
                    }
                }
            }

            // 添加标签关联
            if (tagIds != null && !tagIds.isEmpty()) {
                addBlogTags(conn, blog.getId(), tagIds);
            }

            conn.commit();
            
            // 获取标签列表
            List<Tag> tags = new ArrayList<>();
            try {
                tags = tagDao.findByBlogId(blog.getId());
            } catch (SQLException e) {
                System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
            }
            blog.setTags(tags);
            
            return blog;
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public Blog update(Blog blog, List<Long> tagIds) throws SQLException {
        String sql = "UPDATE blogs SET title = ?, content = ?, category_id = ? WHERE id = ?";

        Connection conn = null;
        try {
            conn = DatabaseManager.getConnection();
            conn.setAutoCommit(false);

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, blog.getTitle());
                pstmt.setString(2, blog.getContent());
                pstmt.setLong(3, blog.getCategoryId());
                pstmt.setLong(4, blog.getId());

                int affectedRows = pstmt.executeUpdate();

                if (affectedRows == 0) {
                    throw new SQLException("Updating blog failed, no rows affected.");
                }
            }

            // 更新标签关联
            if (tagIds != null) {
                // 先删除所有关联
                deleteBlogTags(conn, blog.getId());
                
                // 再添加新的关联
                if (!tagIds.isEmpty()) {
                    addBlogTags(conn, blog.getId(), tagIds);
                }
            }

            conn.commit();
            
            // 获取更新后的标签列表
            List<Tag> tags = new ArrayList<>();
            try {
                tags = tagDao.findByBlogId(blog.getId());
            } catch (SQLException e) {
                System.err.println("Error fetching tags for blog " + blog.getId() + ": " + e.getMessage());
            }
            blog.setTags(tags);
            
            return blog;
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public boolean delete(Long id) throws SQLException {
        String sql = "DELETE FROM blogs WHERE id = ?";

        Connection conn = null;
        try {
            conn = DatabaseManager.getConnection();
            conn.setAutoCommit(false);

            // 删除博客标签关联
            deleteBlogTags(conn, id);

            // 删除博客
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setLong(1, id);
                int affectedRows = pstmt.executeUpdate();
                
                if (affectedRows == 0) {
                    return false;
                }
            }

            conn.commit();
            return true;
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void addBlogTags(Connection conn, Long blogId, List<Long> tagIds) throws SQLException {
        String sql = "INSERT INTO blog_tags (blog_id, tag_id) VALUES (?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (Long tagId : tagIds) {
                pstmt.setLong(1, blogId);
                pstmt.setLong(2, tagId);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
    }

    private void deleteBlogTags(Connection conn, Long blogId) throws SQLException {
        String sql = "DELETE FROM blog_tags WHERE blog_id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, blogId);
            pstmt.executeUpdate();
        }
    }

    private Blog mapResultSetToBlog(ResultSet rs) throws SQLException {
        Blog blog = new Blog();
        blog.setId(rs.getLong("id"));
        blog.setTitle(rs.getString("title"));
        blog.setContent(rs.getString("content"));
        blog.setUserId(rs.getLong("user_id"));
        blog.setCategoryId(rs.getLong("category_id"));
        blog.setCreatedAt(LocalDateTime.parse(rs.getString("created_at")));
        return blog;
    }
}